package cc.messcat.dao.helpunits;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import cc.messcat.common.dao.BaseDaoImpl;
import cc.messcat.common.model.Pager;
import cc.messcat.common.util.BeanUtil;
import cc.messcat.entity.helpUnits.HelpUnits;


@Repository
public class HelpunitsDaoImpl extends BaseDaoImpl<HelpUnits> implements HelpunitsDao {

    @Override
    public List<HelpUnits> retrieveAllarea() {
        // TODO Auto-generated method stub
        return retrieveAllarea();
    }

    @Override
    public List<HelpUnits> getHelpUnitsByCondition(HelpUnits helpUnits) {
        Criteria criteria = getCurrentSession().createCriteria(HelpUnits.class);
        if (BeanUtil.isValid(helpUnits)) {
            if (BeanUtil.isValid(helpUnits.getAffiliation()))
                criteria.add(Restrictions.like("Affiliation", helpUnits.getAffiliation(), MatchMode.ANYWHERE));
            if (BeanUtil.isValid(helpUnits.getUnit_name()))
                criteria.add(Restrictions.like("Unit_name", helpUnits.getUnit_name(), MatchMode.ANYWHERE));
            if (BeanUtil.isValid(helpUnits.getWork_telephone()))
                criteria.add(Restrictions.like("work_telephone", helpUnits.getWork_telephone(), MatchMode.ANYWHERE));
            if (BeanUtil.isValid(helpUnits.getProvince()))
                criteria.add(Restrictions.eq("province", helpUnits.getProvince()));
            if (BeanUtil.isValid(helpUnits.getCity()))
                criteria.add(Restrictions.eq("city", helpUnits.getCity()));
            if (BeanUtil.isValid(helpUnits.getCounty()))
                criteria.add(Restrictions.eq("county", helpUnits.getCounty()));
            if (BeanUtil.isValid(helpUnits.getCountry()))
                criteria.add(Restrictions.eq("country", helpUnits.getCountry()));
        }
        criteria.add(Restrictions.ne("isDel","0"));
        return criteria.list();
    }

	@Override
	public void removeAddhelpoors(Long id, String sid) {

		String sql ="delete from helpunits_and_poorvillage where helpUnitsId= "+id+" and PoorVillageId in ("+ sid+")";
		getCurrentSession().createSQLQuery(sql).executeUpdate();
		
		String sql2 ="update help_team_person set status = '0' where team_id in(select id from help_team where unit_id= "+id+" and village_id in ("+ sid+"))";
		
		getCurrentSession().createSQLQuery(sql2).executeUpdate();
		
	}

	@Override
	public List<?> getHelpUnits(Long id) {

		List list = getCurrentSession().createSQLQuery("select helpUnitsId from helpunits_and_poorvillage where PoorVillageId = "+id).list();
	
		if(list != null && !list.isEmpty()) {
			return getCurrentSession().createQuery(" from HelpUnits where isDel = '1' and  id in("+ Arrays.toString(list.toArray()).replaceAll("[\\[\\]]", "") +")").list();
		}
		return new ArrayList<>();
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> helpUntiStatistics() {
		return getCurrentSession()
		.createSQLQuery("select a.area as name,u.count as value from (select area from area where fatherId='3') as a "
			+ "LEFT JOIN (select country, count(id) as count from HELPUNITS  where  is_del = '1' and Unit_name <> '' group by country) as u on a.area = u.country")
		.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	@Override
	public Pager listHelpUnit(Integer pageNo, Integer pageSize, String sql,String count) {
		int pageStart = (pageNo - 1) * pageSize;
		BigInteger rows = (BigInteger) getCurrentSession().createSQLQuery(count).uniqueResult();
		List list = getCurrentSession().createSQLQuery(sql).setFirstResult(pageStart).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		return new Pager(pageSize,pageNo,rows.intValue(),list);
	}


}

